package dao.mysql;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.AppliedDataDTO;
import model.UserDTO;
import exception.DAOSystemException;
import dao.UserDAO;
import dao.AppliedDataDAO;

//-- Hnin phyho--
public class AppliedDataDAOImpl implements AppliedDataDAO{
	
	MySqlImpl mysqlImpl = new MySqlImpl();
	
	
	public ArrayList<AppliedDataDTO> findAllAppliedUser(String id) throws DAOSystemException {
		ArrayList<AppliedDataDTO> items = new ArrayList<AppliedDataDTO>();
		String selectSql = "Select u.id,u.Name,p.role,l.status,l.applyDate "+
						   "From user as u "+ 
						   "join `leaveapplicationdb`.`position` as p "+
						   "on p.id=u.positionId "+
						   "join `leaveapplicationdb`.`leave` as l "+
						   "on u.id=l.userId "+
						   "join `leaveapplicationdb`.`leavetype` as t "+ 
						   "on t.id=l.leaveTypeId "+ 
						   "Where t.type In('Annual Leave','Medical Leave') "+
						   "and l.status ='Apply' and "+
						   "u.mgrId='"+id+"';";
		
		try {

			
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);			
			String positionId = "";
			String compensationLeaveId = "";
			String managerId = "";
			while (rs.next()) {		
				AppliedDataDTO oneUser = new AppliedDataDTO();
				oneUser.setUserid(rs.getString("id"));
				oneUser.setName(rs.getString("Name"));
				oneUser.setRole(rs.getString("role"));
				oneUser.setStatus(rs.getString("status"));
				oneUser.setAppliedDate(rs.getDate("applyDate"));
				oneUser.setOverTime(0.0);
				items.add(oneUser);
			}
			
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	
	public ArrayList<AppliedDataDTO> findAppliedCompensation(String id) throws DAOSystemException {
		ArrayList<AppliedDataDTO> items = new ArrayList<AppliedDataDTO>();
			String selectSql ="Select u.id,u.Name,p.role,l.status,l.applyDate,c.balanceOT "+
							   "From `leaveapplicationdb`.`user` as u "+
							   "join `leaveapplicationdb`.`compensationleave` as c "+
							   "on u.id=c.id "+ 
						       "join `leaveapplicationdb`.`position` as p "+
							   "on p.id=u.positionId "+
							   "join `leaveapplicationdb`.`leave` as l "+ 
							   "on u.id=l.userId "+ 
							   "join `leaveapplicationdb`.`leavetype` as t "+ 
							   "on t.id=l.leaveTypeId "+
							   "Where l.status='Apply' "+
							   "and t.id='3' "+
							   "and u.mgrId='"+id+"';";
                            
		
		try {

			
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			
			String positionId = "";
			String compensationLeaveId = "";
			String managerId = "";
			while (rs.next()) {
				AppliedDataDTO oneUser = new AppliedDataDTO();
				oneUser.setUserid(rs.getString("id"));
				oneUser.setName(rs.getString("Name"));
				oneUser.setRole(rs.getString("role"));
				oneUser.setStatus(rs.getString("status"));
				oneUser.setAppliedDate(rs.getDate("applyDate"));	
				oneUser.setOverTime(rs.getDouble("balanceOT"));
				items.add(oneUser);
			}
			
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	
}